#!/usr/bin/perl -w  
  
use strict;  
use warnings;
use Spreadsheet::ParseExcel;  
use Path::Class;
use autodie; # die if problem reading or writing a file
use XML::DOM;
use XML::Twig;

our $parser = undef;  
our $workbook = undef;

our @DNList = ();
our $measurementType = undef;
our @counters = ();

our @meaInfo = ();

our $year = "2013";
our $month = "03";
our $day = "15";

sub GenOMesSQLInquireCmdFile{

	open(OutFile,"<nokrww_table.txt") || die("Can not open the file!$!n");
	my @line = <OutFile>;
	close(OutFile);
	
	my @sqlCmd;
	
	# @meaInfo = ("M1021_IUPC", "M1018_RCPMUEQ", "M5107_FATMVP");
	foreach my $meaMid (@meaInfo){
		print "******meaMid: $meaMid\n";
		my @list = split(/_/, $meaMid);
		my $mid = pop(@list);
		
		foreach my $table(@line){
			# print "******table: $table\n";
			
			my $pos = index( $table, "_".$mid."_");
			if($pos != -1){
				$table =~ s/^\s+//;  # romove leading whitespace
				$table =~ s/\s+$//;  # romove trailing whitespace

				my $cmd = "SELECT * FROM NOKRWWRAW.".$table." WHERE RNC_ID='121212000000000063' ORDER BY PERIOD_START_TIME DESC ;";
				my $head = sprintf("%-18s", $meaMid);
				push(@sqlCmd, $head." --- ".$cmd);
			}
		}
	}
	
	open(InFile,">sqlCmd.txt") || die("Can not open the file!$!n");
	foreach my $line ( @sqlCmd ) {
		# Add the line to the file
		print InFile $line."\n";
	}
	close(InFile);
}

sub GenOMesFilesByMeasurement{
	
	my $meaNum = shift;
	my $objectLevel = shift;
	my $meaTypeElemName = shift;
	my $mid = shift;
	# my $meaNum = "M5110";	
	# my @counters = ("M5110C0", "M5110C1", "M5110C2", "M5110C3", "M5110C4", "M5110C5", "M5110C6", "M5110C7", "M5110C8");

	# my $dir = dir("C:/Users/xuuuvqin/Desktop/Perl_study/OMeSFiles"); # /tmp
	my $dir = dir("./OMeSFiles"); # /tmp
	unless( -e $dir ){
		mkdir ($dir);
	}
	
	my $fileHead = "omes_fqdn_PLMN-PLMN%2FOMS-313_NE-OMS-313-";
	my $file_name = $fileHead.$objectLevel."-".$meaNum."_".$mid."-tmpl.xml";
	my $file = $dir->file($file_name); # /tmp/file.txt

	my $file_handle = $file->openw();
	my $xml_doc = XML::DOM::Document->new();
	
	# declaration part "<?xml version="1.0" ?>"	
	my $xmldecl = $xml_doc->createXMLDecl('1.0');
	$xml_doc->setXMLDecl($xmldecl);

	# <OMeS xmlns="pm/cnf_rnc_nsn.5.0.xsd">
	my $omes_elem = $xml_doc->createElement('OMeS');
	$omes_elem->setAttribute('xmlns', "pm/cnf_rnc_nsn.5.0.xsd");
	$xml_doc->appendChild($omes_elem);

	# <PMSetup interval="60" startTime="2013-03-02T17:03:54.000+02:00:00">
	my $interval = "60";
	my $hour=int(rand(24));
    my $minute=int(rand(60));
    my $second=int(rand(60));
    ($hour, $minute, $second) = (sprintf("%02d", $hour), sprintf("%02d", $minute), sprintf("%02d", $second));
	
	my $startTime = "$year-$month-$day"."T"."$hour:$minute:$second.000+02:00:00";
	my $pmsetup_elem = $xml_doc->createElement('PMSetup');
	$pmsetup_elem->setAttribute('interval', $interval);
	$pmsetup_elem->setAttribute('startTime', $startTime);
	$omes_elem->appendChild($pmsetup_elem);	

	# <PMMOResult>
	my $pmmoresult_elem = $xml_doc->createElement('PMMOResult');
	$pmsetup_elem->appendChild($pmmoresult_elem);

	# my @DNList = (1,2);
	my $i = 0;
	my $isnotNE = 0;
	foreach my $dn (@DNList){
		
		# <MO>
		my $moElem = $xml_doc->createElement('MO');
		$pmmoresult_elem->appendChild($moElem);
		
		# <baseId>
		if($i == 0){
			my $baseId = "NE-RNC-313";
			my $basicid_elem = $xml_doc->createElement('baseId');
			$basicid_elem->appendChild($xml_doc->createTextNode($baseId));
			$moElem->appendChild($basicid_elem);
			
			if($dn ne ''){
				my $localMoid = $dn;
				my $pos = index( $localMoid, 'DN:NE-');
				
				if($pos == -1){
					$localMoid = "DN:".$localMoid
				}
				
				# localMoid
				my $localmoid_elem = $xml_doc->createElement('localMoid');
				$localmoid_elem->appendChild($xml_doc->createTextNode($localMoid));
				$moElem->appendChild($localmoid_elem);	
				
				# MOName
				if( $objectLevel eq "RNC" ){
				
					if($pos == -1){
						$isnotNE = 1;
						
						my $moName = "NE-RNC-313_DN:".$dn;
						my $moNameElem = $xml_doc->createElement('MOName');
						$moNameElem->appendChild($xml_doc->createTextNode($moName));
						$moElem->appendChild($moNameElem);	
					}
				}
			}
			# else{
				# if( $objectLevel eq "RNC" ){
					# $isnotNE = 1;
				# }
			# }
			
		}else{
			my $localMoid = "PLMN-PLMN/".$dn;
			my $localmoid_elem = $xml_doc->createElement('DN');
			$localmoid_elem->appendChild($xml_doc->createTextNode($localMoid));
			$moElem->appendChild($localmoid_elem);	
			
			if($isnotNE){
				my $moName = "NE-RNC-313_DN:".$dn;
				my $moNameElem = $xml_doc->createElement('MOName');
				$moNameElem->appendChild($xml_doc->createTextNode($moName));
				$moElem->appendChild($moNameElem);	
			}
				
		}
		
		$i++;	
	}

	# object element
	# my $object = 'WBTS_Nokia_5.0';
	my $object = $meaTypeElemName."_Nokia_5.0";
	my $mea_type = $measurementType;
	my $object_elem = $xml_doc->createElement($object);
	$object_elem->setAttribute('measurementType', $mea_type);
	$pmmoresult_elem->appendChild($object_elem);

	foreach my $line ( @counters ) {
		# Add the line to the file

		next unless $line;
		
		# element for counter value
		
		# my $counter_value = "2013";
		my $counter_value = int(rand(6000));
		my $counter_elem = $xml_doc->createElement($line);

		$counter_elem->appendChild($xml_doc->createTextNode($counter_value));

		$object_elem->appendChild($counter_elem);

	}

	# format the xml string
	my $twig = new XML::Twig;
	$twig->set_indent(" "x4);
	$twig->parse($xml_doc->toString);
	$twig->set_pretty_print("indented");

	# save the xml to a file
	$file_handle->print($twig->sprint);
}

sub GetMeasurementMID{

	my $mesSheet = shift;
	my $row = shift;
	my $col = shift;
	
	my $midCell = $mesSheet->get_cell( $row, $col);
	my $str = $midCell->value();
	$str =~ s/^\s+//;  # romove leading whitespace
	$str =~ s/\s+$//;  # romove trailing whitespace
	
	return $str;
}

sub GetMeasurementObjectLevel{

	my $mesSheet = shift;
	my $row = shift;
	my $col = shift;
	
	my $objectLevel = '';
	my $meaTypeElemName = '';
	
	# e.g. RNC-WBTS-WCEL, MCC-MNC / RNC, MCC-MNC / RNC-ROUTE
	my $objectLevelCell = $mesSheet->get_cell( $row, $col);
	my $str = $objectLevelCell->value();
	if($str eq ''){
		return;
	}else{
		my $pos = index($str, ',');
		if ($pos == -1){
			$str =~ s/^\s+//;  # romove leading whitespace
		    $str =~ s/\s+$//;  # romove trailing whitespace
			# $objectLevel = $str;
		}else{
			$str = substr($str, 0, $pos);
		}
		
		if( index($str, 'FTM') != -1 ){
			$objectLevel = "FTM";
			$meaTypeElemName = "WBTS";
		}elsif( index($str, 'WBTS') != -1 ){
			$objectLevel = "WBTS";
			my @object = split(/-/, $str);
			$meaTypeElemName = pop(@object);
		}else{
			$objectLevel = "RNC";
			$meaTypeElemName = "RNC";
		}
	}
	
	return ($objectLevel, $meaTypeElemName);
}

sub GetMeasurementCounter{
	my $sheetName = shift;
	my $meaNum = shift;
	print "The arg : $sheetName.\n\n";
	next unless $sheetName;
	
	my $counterSheet = $workbook->worksheet($sheetName);
	my ( $rowMin, $rowMax ) = $counterSheet->row_range(); 
	
	my $emptyRowNum = 0;
	for my $row ( $rowMin .. $rowMax) {
		my $cell = $counterSheet->get_cell( $row, 0);
		# next unless $cell;
		
		unless ($cell){
			$emptyRowNum++;
			if($emptyRowNum > 3){
				last;
			}
			
			next;
		}
		
		# get the font color for each cell, if the font color : red, remove this line
		my $colorIndex = $cell->{Format}->{Font}->{Color};
		next if( $colorIndex == 10);
		
		my $counterStr = $cell->value();
		if($counterStr eq ''){
			$emptyRowNum++;
			if($emptyRowNum > 3){
				last;
			}
			
			next;
		}
		
		$emptyRowNum = 0;
		
		my $pos1 = index($counterStr, $meaNum."C");
		my $pos2 = index($counterStr, $meaNum."B");
		next if ($pos1 == -1 && $pos2 == -1);
		$counterStr =~ s/^\s+//;  # romove leading whitespace
		$counterStr =~ s/\s+$//;  # romove trailing whitespace
		push(@counters, $counterStr);
		#print "This counter name : $counterStr.\n";
	}
	
}

sub GetIndexByColName{
	
	my $mes_sheet = shift;
	my $col_name = shift;
	my $row_min = shift;
	my $row_max = shift;
	my $col_min = shift;
	my $col_max = shift;
	
	for my $row ( $row_min .. $row_max) {
		
		for my $col ( $col_min .. $col_max) {
		
			my $cell = ( $mes_sheet->get_cell( $row, $col));
			next unless $cell;
			my $str = $cell->value();
			
			$str =~ s/^\s+//;  # romove leading whitespace
			$str =~ s/\s+$//;  # romove trailing whitespace
			
			if($str eq $col_name){
				return ($row,$col);
			}
		}
	}
	
	exit;
}

sub GetMeasurementDimension{
	
	my $mesSheet = shift;
	my $row = shift;
	my $col = shift;
		
	# demension 1
	my $demension1 = $mesSheet->get_cell( $row, $col);
	my $str1 = $demension1->value();
	if($str1 eq ''){
		return;
	}else{
		my @mngrObjects = split(/-/, $str1);
		
		# remove PLMN-RNC
		my $localMoid = '';
		shift @mngrObjects;
		shift @mngrObjects;
		
		my $i = 0;
		while(@mngrObjects){
			my $object = shift(@mngrObjects);
			next unless $object;
			# DN:NE-WBTS-159/WCEL-1
			
			if( $i == 0 ){
				if( $object eq "WBTS" ){
					$localMoid = "DN:NE-".$object."-159";
				}else{
					# $localMoid = "DN:".$object."-1";
					$localMoid = $object."-1";
				}
			}else{
				# $localMoid = $localMoid.$object."-1";
				$localMoid = $localMoid.$object."-1";
			}
			
			if(@mngrObjects){
				$localMoid = $localMoid."/";
			}
			
			$i++;
		}
		
		push(@DNList, $localMoid);
		
		print "The demension 1 : $localMoid\n";		
	}

	foreach my $index (2..6){
		
		# demension 2-6
		my $demension = $mesSheet->get_cell( $row, $col+$index-1);
		my $str = $demension->value();
		if ($str eq ''){
		
			# print "The DN of this measurement : @DNList\n";
			last;
		}else{

			my @mngrObjects = split(/-/, $str);
			# my $localMoid = "PLMN-PLMN/";
			my $localMoid = '';
			# print "----oraginal mngrObjects 2----@mngrObjects\n";
			
			# remove PLMN			
			shift @mngrObjects;
			while(@mngrObjects){
				my $object = shift(@mngrObjects);
				next unless $object;
				$localMoid = $localMoid.$object."-1";
				
				if(@mngrObjects){
					$localMoid = $localMoid."/";
				}
			}
		
			
			if($localMoid ne ''){
				push(@DNList, $localMoid);
			}
			print "The demension 2 : $localMoid\n";	
		}
	}
	
	print "The DN of this measurement : @DNList\n";

	return;
}

sub GetAllMeasurementsInfo{
	
	my $measurementSheet = $workbook->worksheet("Measurements");
	my ( $row_min, $row_max ) = $measurementSheet->row_range();
	my ( $col_min, $col_max ) = $measurementSheet->row_range();
	
	my ($omesNameRow,$omesNameCol) = GetIndexByColName($measurementSheet, "OMeS Name", $row_min, $row_max, $col_min, $col_max);
	my ($dimension1Row,$dimension1Col) = GetIndexByColName($measurementSheet, "Dimension 1", $row_min, $row_max, $col_min, $col_max);
	# my ($measurementNumRow,$measurementNumCol) = GetIndexByColName($measurementSheet, "Measurement Name", $row_min, $row_max, $col_min, $col_max);
	my ($meaWorksheetRow,$meaWorksheetCol) = GetIndexByColName($measurementSheet, "Measurement worksheet", $row_min, $row_max, $col_min, $col_max);
	my ($objectLevelRow,$objectLevelCol) = GetIndexByColName($measurementSheet, "Level IDs in KOALA", $row_min, $row_max, $col_min, $col_max);
	my ($midRow,$midCol) = GetIndexByColName($measurementSheet, "MID", $row_min, $row_max, $col_min, $col_max);
	
	my $emptyRowNum = 0;
	$row_min = $omesNameRow + 2;
	my $row = $row_min;
	for ( $row_min .. $row_max) {
		my $omesNamecell = $measurementSheet->get_cell( $row, $omesNameCol);
		if($omesNamecell->value() eq ''){
			$row++;
			$emptyRowNum++;
			
			if($emptyRowNum > 2){
				last;
			}
			
			next;	
		}
		$emptyRowNum = 0;
		print "The Row, col = ( $row, $omesNameCol)\n";	
		# get the font color and background color for each cell
		my $colorIndex = $omesNamecell->{Format}->{Font}->{Color};
		my $colorRgb = $parser->ColorIdxToRGB($colorIndex);
		my $bgColorIndex = $omesNamecell->{Format}->{Fill}->[1];
		my $bgColorRgb = $parser->ColorIdxToRGB($bgColorIndex);
		print "The Font index color : $colorIndex. \n";
		# print "Font rgb color : $colorRgb. \n";
		# print "Background color : $bgColorRgb\n";		
		
		# if the font color : red, remove this line
		# next if( $colorIndex == 10);
		if( $colorIndex == 10){
			print "************************************************************\n";
			$row++;
			next;
		}
		
		# get the MO info
		GetMeasurementDimension($measurementSheet, $row, $dimension1Col);
		
		# get measurement type
		$measurementType = $omesNamecell->value();
		$measurementType =~ s/^\s+//;  # romove leading whitespace
		$measurementType =~ s/\s+$//;  # romove trailing whitespace
		print "The measurement type : $measurementType\n";	

		#####################################################################################
		# # get the measurement number		
		# my $measureNumCell = $measurementSheet->get_cell( $row, $measurementNumCol);
		# my $str = $measureNumCell->value();
		# my $pos1 = index($str, "(M");
		# my $pos2 = index($str, ")");
		# next if $pos1 == -1 || $pos2 == -1;
		# my $measurementNum = substr($str, $pos1+1, $pos2-$pos1-1);
		# print "The final str : $measurementNum.\n";
		#
		# #judge whether this cell : merge
		# my $mergeState = $measureNumCell->is_merged();
		# $mergeState = $mergeState == 1? 1:0;
		# print "This cell merge state : $mergeState.\n";
		#####################################################################################
		
		
		
		# get counter sheet name
		my $specCounterFlag = 0;
		my $meaNum = 0;
		my $flag = 1;
		my $num = 0;
		while($flag){
		
			my $counterSheetCell = $measurementSheet->get_cell( $row+$num, $meaWorksheetCol );
			my $counterSheetName = $counterSheetCell->value();

			last unless $counterSheetName;
			
			# get the measurement number
			my $pos1 = index($counterSheetName, ",");
			# next if $pos1 == -1;
			if ($pos1 == -1){
				print "\n";
				last;
			}
			
			my $meaNumStr = substr($counterSheetName, 0, $pos1);
			
			# e.g. M1001_1, M1001_2, M1002
			my $pos2 = index($meaNumStr, "_");
			if ($pos2 == -1){
				$flag = 0;
				
				if($num > 0){
					$row = $row + $num - 1;
					last;
				}
			}else{
				my @numList = split(/_/, $meaNumStr);
				# $meaNumStr = substr($meaNumStr, 0, $pos2);
				$meaNumStr = $numList[0];
				if($numList[1] > $num){
					$num = $numList[1];
					$flag = 1;
				}else{
					$flag = 0;
					$row = $row + $num - 1;
					last;
				}
				
			}
			
			my $pos3 = index($meaNumStr, "M");
			if ($pos3 == -1){
				$specCounterFlag = 1;
				print "\n";
				last;
			}
			
			$meaNum = $meaNumStr;
			
			print "The counter sheet name : $counterSheetName.\n";
			print "The measurement number : $meaNum.\n";
			GetMeasurementCounter($counterSheetName, $meaNum);
		}
		
		unless(@counters){
			undef @DNList;
			undef @counters;
			$row++;
			next;
		}
		
		if($specCounterFlag != 1){
			my ($objectLevel, $meaTypeElemName) = GetMeasurementObjectLevel($measurementSheet, $row, $objectLevelCol);
			my $mid = GetMeasurementMID($measurementSheet, $row, $midCol);
			GenOMesFilesByMeasurement($meaNum, $objectLevel, $meaTypeElemName, $mid);

			push(@meaInfo, $meaNum."_".$mid);
		}
		
		undef @DNList;
		undef @counters;
		$row++;
		print "************************************************************\n";
	}
}

sub AutoGenerateOMesFilesByAD {
	
	$parser = Spreadsheet::ParseExcel->new();  
	$workbook = $parser->Parse('Adaptation Description RU40 OSS54 CD3.xls');
	
	GetAllMeasurementsInfo;
	GenOMesSQLInquireCmdFile;
}

AutoGenerateOMesFilesByAD;